﻿/*
padrao:
--- Descricao da funcao
-- exemplo que funcione.
-- @param [nome]tipo - descricao
-- @return tipo - descricao

nomenclatura - prefixo:
fn = funcao
*/

--- funcao de nada. soh pra achar tudo de string. 
-- ex: select fn_NoCase('Abc') --> vira '%abc%'
-- @param text - um texto qualquer
-- @return text - um texto pra fazer consultas com like.
create or replace function fn_NoCase(text) returns text
AS $$
	select lower('%' || $1 || '%');
$$ LANGUAGE SQL;

--- Funcao para pegar o id de um cliente atraves do nome
-- ex: Select * from fn_getClienteId('Eidy')
-- @param string - nome do cliente
-- @return int - o codigo do cliente
CREATE OR REPLACE FUNCTION fn_getClienteId(text) RETURNS bigint AS $$
	select CodCliente from Cliente where nomecli = $1;
$$ LANGUAGE SQL;

--- Funcao para pegar o id de um profissional atraves do nome
-- ex: Select * from fn_getProfissionalId('Diogo Hideki Matsumoto')
-- @param string - nome do profissional
-- @return int - o codigo do profissional
CREATE OR REPLACE FUNCTION fn_getProfissionalId(text) RETURNS int AS $$
	select CodProfissional from Profissional where nome = $1;
$$ LANGUAGE SQL;


--- Funcao verificar se um CPF ja existe em profissionais
-- ex: Select * from fn_existe_profissional_CPF('111.111.111-11') 
-- @param string - CPF a ser procurado
-- @return boolean - true se ja existir o CPF do profissional
CREATE OR REPLACE FUNCTION fn_existe_profissional_CPF(text) RETURNS boolean AS $$
	select count(cpf) > 0 from profissional where cpf = $1
$$ LANGUAGE SQL;

--- Funcao verificar se um login ja existe em users
-- ex: Select * from fn_existe_users_Login('admin') 
-- @param string - login a ser procurado
-- @return boolean - true se ja existir o login do users
CREATE OR REPLACE FUNCTION fn_existe_users_login(text) RETURNS boolean AS $$
	select count(login) > 0 from users where login = $1
$$ LANGUAGE SQL;

--- Funcao verificar se um login ja existe em users
-- ex: Select * from fn_existe_cliente_cpfcnpj('222222222222222222') 
-- @param string - CPF a ser procurado
-- @return boolean - true se ja existir o CPF do profissional
CREATE OR REPLACE FUNCTION fn_existe_cliente_cpfcnpj(text) RETURNS boolean AS $$
	select count(cpfcnpj) > 0 from cliente where cpfcnpj = $1
$$ LANGUAGE SQL;

--- funcao q lista todas as especializacores de um profissional pelo nome
-- ex: select * from fn_lista_especializacao('Diogo Hideki Matsumoto')
-- @param text - nome EXATO
-- @return tp_profissional_profissao - os dados do profissional
CREATE OR REPLACE FUNCTION fn_lista_especializacao(text) RETURNS SETOF tp_profissional_profissao AS $$
	select * from vw_profissional_profissao
		where nome = $1;
$$ LANGUAGE SQL;

--- funcao q lista todas as especializacores de um profissional pelo nome, com busca avancada
-- ex: select * from fn_lista_especializacao_avancado('diogo')
-- @param text - nome do profissional. sem case sensitive, tudo que contenha;
-- @return tp_profissional_profissao - os dados do profissional
CREATE OR REPLACE FUNCTION fn_lista_especializacao_avancado(text) RETURNS SETOF tp_profissional_profissao AS $$
	select * from vw_profissional_profissao
		where lower(nome) like lower('%' || $1 || '%');
$$ LANGUAGE SQL;

--- Funcao para atualizar o nivel de Especializacao de um profissional
-- select fn_atualizaNivel(1,1,2)
-- @param int - Codigo Profissional
-- @param int - Codigo Profissao
-- @param int - nivel a adcionar.
-- @return int - o novo nivel
CREATE OR REPLACE FUNCTION fn_atualizaNivel(int, int, int) RETURNS smallint AS $$
	-- atualizando
	UPDATE Especializacao set nivel = nivel + $3 
		WHERE ProfissionalCodProfissional = $1 AND profissaoCodProfissao = $2;
	
	-- retornando nivel
	select nivel from vw_profissional_profissao
		where codprofissional = 1 and codprofissao = 1
		limit 1
$$ LANGUAGE SQL;


--- Funcao para atualizar o nivel de Especializacao de um profissional
-- select * from fn_atualizaNivelContrato(6,1,3);
-- @param int - Codigo Solicitacao
-- @param int - Codigo Profissional
-- @param int - nivel a setar.
-- @return int - o novo nivel
CREATE OR REPLACE FUNCTION fn_atualizaNivelContrato(int, int, int) RETURNS boolean AS $$
	--select * from ClientesProfissional
	--select * from vw_profissional_profissao where codprofissional = 1
BEGIN
	update ClientesProfissional set nivel = $3 where SolicitacaoCodSolicitacao = $1 and ProfissionalCodProfissional = $2;
	RETURN FOUND;
END;
$$ LANGUAGE plpgsql;

--- Funcao para atualizar o nome de uma area
-- select fn_atualizar_area(1,'NovaArea')
-- @param int - Codigo da area a alterar
-- @param text - novo nome da area.
-- @return booleano - true se alterou.
CREATE OR REPLACE FUNCTION fn_atualiza_area(integer, novoNome text) RETURNS boolean AS $$
DECLARE
	var_sistema integer; -- variavel q recebe valores do sistema.
BEGIN
	-- atualizando
	update area set descricao = novoNome where codarea = $1;
	
	GET DIAGNOSTICS var_sistema := ROW_COUNT;
	RETURN var_sistema > 0;
END;
$$ LANGUAGE plpgsql;

--- Funcao para atualizar uma profissao
-- begin;select fn_atualizar_profissao(1,'analogo', 2);select * from profissao;rollback;
-- @param int - Codigo da area a alterar
-- @param text - novo nome da area.
-- @return booleano - true se alterou.
CREATE OR REPLACE FUNCTION fn_atualizar_profissao(integer, novoNome text, novaArea int) RETURNS boolean AS $$
DECLARE
	var_sistema integer; -- variavel q recebe valores do sistema.
BEGIN
	-- atualizando
	
	update profissao p set descricao = novoNome, areaCodArea = novaArea where codProfissao = $1;
	
	GET DIAGNOSTICS var_sistema := ROW_COUNT;
	RETURN var_sistema > 0;
END;
$$ LANGUAGE plpgsql;


--- funcao q busca uma profissao pelo codigo.
-- ex: select * from fn_busca_profissao(1)
-- @param int - codigo da profissao
-- @return - tp_solicitacoes - os dados de todas as solicitacoes do cliente
CREATE OR REPLACE FUNCTION fn_busca_profissao(int) RETURNS SETOF vw_area_profissao AS $$

	select * from vw_area_profissao
		where codProfissao = $1
		Limit 1;
$$ LANGUAGE SQL;

--- funcao q lista todas as solicitacoes de um cliente pelo nome
-- ex: select * from fn_lista_solicitacoes('shi')
-- @param text - nome do cliente. sem case sensitive, tudo que contenha;
-- @return - tp_solicitacoes - os dados de todas as solicitacoes do cliente
CREATE OR REPLACE FUNCTION fn_lista_solicitacoes(text) RETURNS SETOF tp_solicitacoes AS $$
	select * from vw_solicitacoes 
		where lower(cliente) like lower('%' || $1 || '%')
$$ LANGUAGE SQL;

--- funcao q lista todas as solicitacoes que foram ou nao atendidas.
-- ex: select * from fn_lista_solicitacoes(false)
-- @param codCliente int - codigo do cliente para filtrar.
-- @param atendida boolean - true para filtrar apenas as atendidas.
-- @return - tp_solicitacoes - os dados de todas as solicitacoes do cliente
CREATE OR REPLACE FUNCTION fn_lista_solicitacoes(codCliente int, atendida boolean) RETURNS SETOF tp_solicitacoes AS $$
DECLARE
BEGIN
	
	if atendida then
		return query
			select * from vw_solicitacoes t
				where DataAtendimento > DataSolicitacao
					and t.CodCliente = codCliente
				;
	else
		return  query
			select * from vw_solicitacoes t
				where DataAtendimento < DataSolicitacao 
					or DataAtendimento is null
					and t.CodCliente = codCliente
					;
	end if;
	
END;
$$ LANGUAGE plpgsql;


--- funcao para adcionar uma area e uma profissao. caso ja tenha a area nao eh criado uma nova.
-- ex1: select * from fn_inserir_profissao('teste', 'testador'); -- cria area 'teste' caso nao tenha e adciona profissao 'testador'
-- ex2: select * from fn_inserir_profissao('teste', 'testador2'); -- adciona profissao 'testador2' na area 'teste'
-- @param nova_area text - 
-- @param nova_profissao text - 
CREATE OR REPLACE FUNCTION fn_inserir_profissao(nova_area text, nova_profissao text) RETURNS boolean AS $$
DECLARE
	cod_area integer;
	var_sistema integer; -- variavel q recebe valores do sistema.
BEGIN
	-- verificar se existe area
	select into cod_area * from area where area.Descricao = nova_area;

	if FOUND then
		-- inserir profissao na area
		INSERT INTO Profissao(Descricao, AreaCodArea) VALUES (nova_profissao, cod_area);
	else
		-- criar area e inserir (recursivo)
		INSERT INTO Area(Descricao) VALUES (nova_area);
		return fn_inserir_profissao(nova_area, nova_profissao);
	end if;
	GET DIAGNOSTICS var_sistema := ROW_COUNT;
	
	return var_sistema > 0;
END;
$$ LANGUAGE plpgsql;

--- funcao pra pegar a quantidade de de solicitacoes de um cliente
-- ex: select * from fn_quantidade_solicitacoes('Shishido')
-- @param text - o nome do cliente
-- @return - tp_solicitacoes - os dados de todas as solicitacoes do cliente
CREATE OR REPLACE FUNCTION fn_quantidade_solicitacoes(text) RETURNS TABLE(codcliente bigint, cliente character varying(100), quantidade bigint) AS $$
	select codcliente, cliente, sum(quantidade) from vw_solicitacoes 
		--where lower(cliente) like lower('%' || $1 || '%')
		where cliente = $1
		group by codcliente,cliente
$$ LANGUAGE sql;


--- funcao pra saber o codigo da area de uma profissao baseado no nome da profissao
-- ex: select fn_get_area_profissao('testador')
-- @param text - o nome EXATO do nome da profissao
-- return integer - o codigo da area da profissao
CREATE OR REPLACE FUNCTION fn_get_area_profissao(text) RETURNS integer AS $$
	select AreaCodArea from Profissao where Profissao.Descricao = $1 limit 1;
$$ LANGUAGE sql;

--- funcao pra adcionar uma nova espcializacao para um profissional
-- ex: select fn_inserir_especializacao('Aline Yukita', 'Programador', current_date, 10);
-- @param novo_profissional text - nome do profissional
-- @param nova_profissao text - nome da profissao
-- @param data_especializacao date - data q o profissional se especializou
-- @param novo_nivel integer - nivel a ser iniciado pela especializacao;
CREATE OR REPLACE FUNCTION fn_inserir_especializacao(novo_profissional text, nova_profissao text, data_especializacao date, novo_nivel integer) RETURNS boolean AS $$
DECLARE
	cod_profissional integer;
	cod_profissao integer;
	var_sistema integer; -- variavel q recebe valores do sistema.
BEGIN
	-- verifica se tem a area da profissao
	select into cod_profissao CodProfissao from Profissao where Profissao.Descricao = nova_profissao;
	
	if FOUND then
		-- verifica se tem o profissional
		cod_profissional := fn_getProfissionalId(novo_profissional);
		--raise notice 'quanto deu o found %, %', FOUND, cod_profissional;
		--raise notice 'FOUND and % = %', (cod_profissional), (FOUND and cod_profissional>0);
		if (FOUND and cod_profissional>0) then
			--raise notice 'INSERT INTO Especializacao (ProfissionalCodProfissional, ProfissaoCodProfissao, DataEspecializacao, Nivel) VALUES (%,%,%,%);', cod_profissional, cod_profissao, data_especializacao, novo_nivel;
			INSERT INTO Especializacao
				(ProfissionalCodProfissional, ProfissaoCodProfissao, DataEspecializacao, Nivel) 
				VALUES (cod_profissional, cod_profissao, data_especializacao, novo_nivel);	
			GET DIAGNOSTICS var_sistema := ROW_COUNT;
			return var_sistema > 0;
		end if;
	end if;
	return false;
END;
$$ LANGUAGE plpgsql;

--- funcao para filtrar a visao de area e quantidade
-- ex: select * from fn_filtrar_area_quantidade('analista', 'aline')
-- @param text - nome da profissao
-- @param text - nome do profissional
CREATE OR REPLACE FUNCTION fn_filtrar_area_quantidade (text, text) RETURNS SETOF vwr_profissionais_solicitacao
AS $$
	select * from vwr_profissionais_solicitacao
		where lower(profissao) like fn_NoCase($1)
		and lower(nome) like fn_NoCase($2)

$$ LANGUAGE SQL;



--- funcao para filtrar a visao de area e quantidade
-- ex: select * from fn_filtrar_area('analista')
-- @param text - nome da area para filtrar
CREATE OR REPLACE FUNCTION fn_filtrar_area (text) RETURNS SETOF Area
AS $$
	SELECT * FROM Area WHERE LOWER(descricao) LIKE fn_NoCase($1)
$$ LANGUAGE SQL;


--- funcao para filtrar a visao de area e quantidade
-- ex: select * from fn_filtrar_profissao(2)
-- @param text - codigo da area para filtrar
CREATE OR REPLACE FUNCTION fn_filtrar_profissao (int) RETURNS SETOF Profissao
AS $$
	select * from Profissao where AreaCodArea = $1 order by codProfissao
$$ LANGUAGE SQL;

--- funcao para filtrar a visao de area e quantidade
-- ex: select * from fn_filtrar_profissao('m')
-- @param text - filtrar descricao. tudo q contenha.
CREATE OR REPLACE FUNCTION fn_filtrar_profissao (text) RETURNS SETOF Profissao
AS $$
	select * from Profissao where lower(descricao) like fn_NoCase($1) order by codProfissao; 
	--select * from  vw_area_profissao where lower(profissao) like fn_NoCase($1);
$$ LANGUAGE SQL;


--- funcao q lista todos os especialistas de uma profissao pelo id da profissao
-- ex: select * from fn_lista_especialista(1, false)
-- @param int - Codigo da profissao
-- @return tp_profissional_profissao - os dados do profissional
CREATE OR REPLACE FUNCTION fn_lista_especialista(int, boolean) RETURNS SETOF tp_profissional_profissao AS $$
	select * from vw_profissional_profissao 
		where codprofissao = $1
			and alocado = $2
			order by nivel desc
$$ LANGUAGE SQL;


--- funcao q lista todos os profissionais disponiveis dentro de uma array de codigos de profissional, que possuem certa profissao
-- ex: select * fn_filtra_profissionais(ARRAY[1,2,3],3);
-- @param int[] - array de profissionais que se quer saber
-- @param int - Codigo da profissao
-- @return SETOF profissional;
CREATE OR REPLACE FUNCTION fn_filtra_profissionais(int[], int) 
RETURNS SETOF Profissional AS $$
	select * from vw_profissionais_disponiveis v
		where 	v.codProfissional = ANY($1)
				and v.codProfissional IN (SELECT codProfissional FROM vw_profissional_profissao where CodProfissao = $2)
$$ LANGUAGE SQL;

--- funcao q lista todos os profissionais disponiveis dentro de uma array de codigos de profissional, que possuem certa profissao
-- ex: select * from fn_filtra_cliente_profissional(6)
-- @param int - Codigo da Solicitacao
-- @param int - Codigo do Cliente
-- @return SETOF profissional;
CREATE OR REPLACE FUNCTION fn_filtra_cliente_profissional(int, int) 
RETURNS SETOF tp_cliente_profissional AS $$
	select * from vw_cliente_profissional where codSolicitacao = $1 and codCliente = $2;
$$ LANGUAGE SQL;
-- select * from vw_cliente_profissional


--- funcao para encerrar uma solicitacao e contratar um pessoal para essa solicitacao.
--- soh eh possivel contratar se a quantidade de profissionais solicitado bater com os profissionais indicados.
-- ex: select * from fn_inserir_solicitacao(5, ARRAY[1,2,3,4])
-- @param codSolicitacao integer - o codigo da solicitacao
-- @param indicacoes integer[] - array com os codigos de profissionais indicados
-- @return int - caso sucedido retorna 1, senao retorna -1,-2. etc
CREATE OR REPLACE FUNCTION fn_inserir_solicitacao(codSolicitacao int, indicacoes integer[] )
RETURNS integer AS $$
DECLARE
	row_solicitacao record; -- aqui eh para saber se pode contratar.  (qntde = indicacoes.length, DataAtendimento < hj ou nao nulo
	tempProf record; -- iterador
	
	qtdeProf integer; -- quantidade de profissinais disponiveis para alocar (baseado na array indicacoes)
	var_sistema integer; -- variavel q recebe valores do sistema.
	
BEGIN
	
	select into row_solicitacao * from Solicitacao s where s.CodSolicitacao = codSolicitacao;
	if FOUND then
		RAISE NOTICE 'row_solicitacao = %, %, %, %, %', row_solicitacao.ClienteCodCliente, row_solicitacao.ProfissaoCodProfissao, row_solicitacao.Qtde, row_solicitacao.DataSolicitacao, row_solicitacao.DataAtendimento;
		
		
		-- ja foi atendida?
		if (row_solicitacao.DataAtendimento is null) then
			-- nao, entao tenta contratar os caras.

			-- vendo quantas pessoas podem ser contradadas..
			select into qtdeProf COUNT(*) from fn_filtra_profissionais(indicacoes, row_solicitacao.ProfissaoCodProfissao);

			-- o numero de pessoas disponiveis batem com o numero solicitado?
			if (qtdeProf = row_solicitacao.qtde) then
				-- sim, entao contrata!
				for tempProf in select codprofissional from fn_filtra_profissionais (indicacoes, row_solicitacao.ProfissaoCodProfissao) loop
					-- RAISE NOTICE 'INSERT INTO ClientesProfissional(SolicitacaoCodSolicitacao, ProfissionalCodProfissional, Aceito, Nivel) values (%,%,%,%)', row_solicitacao.codSolicitacao, tempProf.codProfissional, true, 1;
					INSERT INTO ClientesProfissional(SolicitacaoCodSolicitacao, ProfissionalCodProfissional, Aceito, Nivel) values (row_solicitacao.codSolicitacao, tempProf.codProfissional, true, 1) ;
				end loop;
				
				RAISE NOTICE 'Contratacao sucedida';
				return 1;
			else 
				-- nao, entao avisa quandos precisa de verdade
				RAISE NOTICE 'achou % mas precisa de %', qtdeProf, row_solicitacao.qtde;
				
				-- quantidade nao bate
				return -1;
			end if;
				
			
			RAISE NOTICE 'achou: %', qtdeProf;
		end if;
		-- sim, entao retorna false
		
		-- solicitacao ja atendida
		RETURN -2;
	end if;
	-- solicitacao nao encontrada.
	RETURN -3; 
	--INSERT INTO ClientesProfissional(SolicitacaoCodSolicitacao, ProfissionalCodProfissional, Aceito, Nivel) 
		--values();
	
END;
$$ LANGUAGE plpgsql;


--- soh eh possivel deletar se nao tiver nenhum profissional com uma profissao q tenha essa area
-- ex: select * from fn_deletar_area(1)
-- @param codArea integer - o codigo da area a deletar
-- @return boolean - true se deletou.
CREATE OR REPLACE FUNCTION fn_deletar_area(codArea integer)
RETURNS boolean AS $$
DECLARE
	var_sistema integer; -- variavel q recebe valores do sistema.	
BEGIN
	delete from profissao where AreaCodArea = codArea;
	delete from area where area.codArea = codArea;
	GET DIAGNOSTICS var_sistema := ROW_COUNT;
	return var_sistema > 0;
END;
$$ LANGUAGE plpgsql;


--- soh eh possivel deletar se nao tiver nenhum profissional com essa profissao
-- ex: select * from fn_deletar_profissao(1)
-- @param codArea integer - o codigo da area a deletar
-- @return boolean - true se deletou.
CREATE OR REPLACE FUNCTION fn_deletar_profissao(integer)
RETURNS boolean AS $$
DECLARE
	var_sistema integer; -- variavel q recebe valores do sistema.	
BEGIN
	delete from profissao where CodProfissao = $1;
	GET DIAGNOSTICS var_sistema := ROW_COUNT;
	return var_sistema > 0;
END;
$$ LANGUAGE plpgsql;
